Constant Values |
| They refer to fixed values that cannot be altered by the program. PL/SQL constants can be of any of the basic data types. Character constants are enclosed between single quotes. Constants are very useful for enforcing sage and disciplined code development in large and complex applications. For instance, the stored procedure shown below will not compile. Oracle permite declarar valores fijos que no pueden ser alterados por el programa. Las constantes PL/SQL pueden ser de cualquier de los tipos básicos de datos. Las constantes de texto se inicializan con texto entre apostrofes. Las constantes son muy útiles en códigos complejos y largos para asegurar que serán modificadas. Por ejemplo, el procedimiento almacenado mostrado debajo no compilará. |
| MSDOS: cmd.exe |
| SQL> CREATE OR REPLACE PROCEDURE p_incorrect_procedure 2 IS 3 inch_per_feet CONSTANT NUMBER(2, 0) := 12; 4 BEGIN 5 inch_per_feet := 10; 6 END p_incorrect_procedure; 7 / Warning: Procedure created with compilation errors. SQL> show errors; Errors for PROCEDURE P_INCORRECT_PROCEDURE: LINE/COL ERROR -------- ----------------------------------------------------------------- 5/4 PL/SQL: Statement ignored 5/4 PLS-00363: expression 'INCH_PER_FEET' cannot be used as an assignment target |
| Tip |
| Microsoft SQL Server does not support constant values. Microsoft SQL Server no proporciona valores contantes. |
| Problem 1 |
| kimberly > Write a stored procedure called p_circle to calculate the diameter, the circumference, and the area of a circle. The procedure should take the radius of the circle as a parameter. The procedure should output the diameter, the circumference, and the area. Use a constant variable to store the value of π. Use Oracle. Escriba un procedimiento almacenado llamado p_circle para mostrar el diámetro, perímetro y área de un círculo. El procedimiento tiene como entrada el radio del círculo. El procedimiento debe usar el comando CONSTANT para definir el valor de π. Use Oracle. |
| MSDOS: cmd.exe |
| SQL> connect kimberly Enter password: Connected. SQL> SET SERVEROUTPUT ON; SQL> EXECUTE p_circle(10, 'cm'); Diameter: 20 cm Circumference: 62.8318 cm Area: 314.159 sq-cm PL/SQL procedure successfully completed. SQL> EXECUTE p_circle(20, 'feet'); Diameter: 40 feet Circumference: 125.6636 feet Area: 1256.636 sq-feet PL/SQL procedure successfully completed. |
| Problem 2 |
| kimberly > Write a stored procedure called p_matinc to increase 3.4 % the price of all the products from the material table. This procedure will destroy the original data of your original table. You may want to restore your database by running your setup scripts after completion of this activity. Use Oracle and define a constant value for the price increase. You must use the CONSTANT command. Escriba un procedimiento almacenado llamado p_matinc para incrementar el precio en un 3.4% de todos los productos de la tabla material. Este procedimiento destruirá los datos originales de la tabla. Una vez terminada esta actividad usted debe restaurar la base de datos original ejecutando el script original. Use Oracle y defina un valor constante para el incremento de precio. Usted debe usar el comando CONSTANT. |
| MSDOS: cmd.exe |
| SQL> SELECT * FROM material; MA DESCR COST DENSITY -- ---------- ---------- ---------- PA Paper 1.5 .57 PL Plastic 2.5 1.82 WO Wood 3.53 .79 WL Wool 7.5 .28 ME Metal 9.87 5.52 SQL> EXECUTE p_matinc; PL/SQL procedure successfully completed. SQL> SELECT * FROM material; MA DESCR COST DENSITY -- ---------- ---------- ---------- PA Paper 1.55 .57 PL Plastic 2.59 1.82 WO Wood 3.65 .79 WL Wool 7.76 .28 ME Metal 10.21 5.52 |
| Problem 3 |
| kimberly > Repeat the previous problem using Microsoft SQL Server.. Repita el problema anterior usando Microsoft SQL Server. |
| Microsoft SQL Server |
| SELECT material_id, descr, cost, density FROM material; |
| Microsoft SQL Server |
| USE kimberly; EXECUTE p_matinc; |
| Microsoft SQL Server |
| SELECT material_id, descr, cost, density FROM material; |
| Problem 4 |
| kimberly > Write a stored procedure called p_matden to increase 2.1% the price of those products that have a density bigger than one from the material table. Otherwise the procedure must decrease the price by 4.53%. Be sure you are using the original values in the material table. This procedure will destroy the original data in the table. Once the problem is completed, be sure to restore the data of the table using the original script of the database. Using: (a) Oracle. (b) Microsoft SQL Server. Escriba un procedimiento almacenado llamado p_matden para incrementar el precio en un 2.1% de todos los productos de la tabla material que tengan una densidad mayor a 1. De otra forma el procedimiento debe disminuir el precio en 4.53%. Asegúrese de usar los valores originales de la tabla material. Este procedimiento destruirá los datos originales de la tabla. Una vez terminado este problema, usted debe restaurar la base de datos original ejecutando el script. Usando: (a) Oracle. (b) Microsoft SQL Server. |
| MSDOS: cmd.exe |
| SQL> SELECT material_id, 2 descr, 3 cost, 4 density 5 FROM material; MA DESCR COST DENSITY -- ---------- ---------- ---------- PA Paper 1.5 .57 PL Plastic 2.5 1.82 WO Wood 3.53 .79 WL Wool 7.5 .28 ME Metal 9.87 5.52 SQL> EXECUTE p_matden; PL/SQL procedure successfully completed. SQL> SELECT material_id, 2 descr, 3 cost, 4 density 5 FROM material; MA DESCR COST DENSITY -- ---------- ---------- ---------- PA Paper 1.43 .57 PL Plastic 2.55 1.82 WO Wood 3.37 .79 WL Wool 7.16 .28 ME Metal 10.08 5.52 |
| Microsoft SQL Server |
| SELECT material_id, descr, cost, density FROM material; |
| Microsoft SQL Server |
| USE kimberly; EXECUTE p_matden; |
| Microsoft SQL Server |
| SELECT material_id, descr, cost, density FROM material; |
